package zz.extjs.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONObject;

import sun.management.counter.Variability;

import zz.extjs.factory.DAOFactory;
import zz.extjs.vo.Student;

public class GetStudentList extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		this.doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/html; charset=utf-8");
		PrintWriter out = response.getWriter();

		// 登录检查
		Object zz = request.getSession().getAttribute("user1");

		if (zz == null) { // 先检查用户是否处于登入状态
			response.sendRedirect("logout.jsp");
			return;
		}
		
		int recordcount = 0;
		String tab = request.getParameter("tab"); // querystring
													// 参数，成绩以外时出现，pwmng是发送密码字段
		String subname = request.getParameter("subname"); // 成绩查询时出现，科目名称

		// 无参传入，返回没有成绩的所有记录
		String sql = "SELECT p.*,c.*, t_phone EXAM_ID, t_phone scope_tab, t_phone SUB_NAME FROM PERSON_INFO_TAB p left join COURSE_TAB c on p.COS_ID=c.COS_ID ";
		// 使用t_phone EXAM_ID，因为t_phone是空，使用保证exam_id皆空
		sql += " order by p.T_SID";
		if ("totalresult".equals(tab) || "singleresult".equals(tab)) {
			if (subname != null) {
				if ("all".equals(subname)) {
					// 查询全部成绩sql
					sql = "SELECT * FROM PERSON_INFO_TAB p left join COURSE_TAB c on p.COS_ID=c.COS_ID ";
					sql += " left join EXAM_LIST_TAB e on p.PER_ID=e.PER_ID left join SUBJECT_TAB s on e.SUB_ID=s.SUB_ID ";
					sql += " order by p.T_SID";
				} else {
					// 查询单科成绩，重写sql
					sql = "SELECT * FROM PERSON_INFO_TAB p left join COURSE_TAB c on p.COS_ID=c.COS_ID ";
					sql += " left join (select e.EXAM_ID,e.per_id,e.scope_tab,s.s.SUB_NAME from exam_list_tab e left join SUBJECT_TAB s on e.SUB_ID=s.SUB_ID ";
					sql += "where s.SUB_NAME='" + subname
							+ "') t on p.per_id=t.per_id ";
					sql += " order by p.T_SID";
				}
			}else{
				// 无参传入，返回没有成绩的所有记录
				sql = "SELECT p.*,c.*, t_phone EXAM_ID, t_phone scope_tab, t_phone SUB_NAME FROM PERSON_INFO_TAB p left join COURSE_TAB c on p.COS_ID=c.COS_ID ";
				// 使用t_phone EXAM_ID，因为t_phone是空，使用保证exam_id皆空
				sql += " order by p.T_SID";
			}
		} else if ("personinfo".equals(tab) || "passmng".equals(tab)) {
			// 查询个人信息
			sql = "SELECT * FROM PERSON_INFO_TAB p left join COURSE_TAB c on p.COS_ID=c.COS_ID ";
			sql += " order by p.T_SID";
		} else if("crs_stu_lst".equals(tab)){
			String term = request.getParameter("term");
			String start = request.getParameter("start");
			String limit = request.getParameter("limit");
			if(term != null && !"".equals(term.trim())){
				// 查询历史信息，term是学期
				sql = "SELECT p.*,c.* FROM dusc"+term.trim()+".PERSON_INFO_TAB p left join dusc"+term.trim()+".COURSE_TAB c on p.COS_ID=c.COS_ID ";
				sql += " order by p.T_SID";
			}
		} else if("crs_status_mng".equals(tab)){
			// 专门sevlet处理
			String term = request.getParameter("term");
			sql = " )";
			if(term != null && !"".equals(term.trim()) && !"all".equals(term.trim()))
				sql = " where t"+term.trim()+"<>'-' ) ";
				
			/*****************添加分页处理，标志是参数sqlwhere含有“where"*****************/	
			String start = request.getParameter("start");
			String limit = request.getParameter("limit");
			if(start!=null && limit!=null && !"".equals(start) && !"".equals(limit) && !"null".equals(start) && !"null".equals(limit))
				sql += " where (n > "+start+" and n <= "+start+"+" + limit+ ") ";
			else 
				sql +=" where 1=1 ";
		}

		//Iterator<Student> result;
		Iterator<JSONObject> result;
		try {
			if ("totalresult".equals(tab) || "singleresult".equals(tab))// &&subname!=null)
				result = DAOFactory.getStudentInstance().getExamList(sql);
			else if ("crs_status_mng".equals(tab)){
				String sqlcount = sql+" or 1= 1";
				result = DAOFactory.getCrossSchemaInstance().getAllTermList(sqlcount);
				while (result.hasNext()) {
					recordcount++;
					result.next();
				}
				result = DAOFactory.getCrossSchemaInstance().getAllTermList(sql);
			}else
				result = DAOFactory.getStudentInstance().getList(sql);

			// 拼装json。。。
			if (result != null && result.hasNext()) {
				// q="";
				JSONArray jsonArr = new JSONArray();
				while (result.hasNext()) {
					//Student stu = (Student) result.next();
					JSONObject jsontemp = (JSONObject) result.next();
					if (!"pwmng".equals(tab)) // 非pemng页面时屏蔽pw字段
						jsontemp.put("pw", "");
						//stu.setPw(null);
					//JSONObject jsontemp = new JSONObject(stu);
					if(jsontemp.has("results")){
						JSONArray rets = (JSONArray) (jsontemp.get("results"));		//成绩查询时有效
						if (rets != null && rets.length() > 0) {
							JSONObject jo = rets.getJSONObject(0);
							if (jo.getString("subname") != null) {
								jsontemp.remove("results");
								jsontemp.put("subname", jo.getString("subname"));
								jsontemp.put("result", jo.getString("result"));
							}
						}
					}

					jsonArr.put(jsontemp);
				}
				//out.print("{results:" + jsonArr.length() + ",rows:");
				out.print("{results:"+recordcount+",rows:");
				out.print(jsonArr.toString());
				out.print("}");
				out.flush();
				out.close();

				// form.reset(mapping, request);

			}
		} catch (Exception e) {
			throw new NullPointerException("getstudentlist出错");
		}
	}
}
